/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package giormi;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.swing.JOptionPane;
import javax.swing.JTable;
/**
 *
 * @author giorgio
 */
public class searchorder {

private String input;
private String searchbywhat;
private JTable otable;



public searchorder(String input, String searchbywhat,JTable otable){
    this.input = input;
    this.searchbywhat = searchbywhat;
    this.otable = otable;
}


  
    private Connection conn;
    private String SQLStatement;
    private String URL;
    private Statement stmt;
    public ResultSet rset;    
    
public void connect(){
        try{
            //1
//            URL = "jdbc:odbc:introdb";
//            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//            conn = DriverManager.getConnection(URL,"root","huhu");
            DBConnection db = new DBConnection();
            conn = DBConnection.get();
            //2
            stmt = conn.createStatement();
        }
        catch(Exception e){
            System.out.println(e.getMessage());
            JOptionPane.showMessageDialog(null,e.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }

    public void disconnect(){
        try{
            //5
            stmt.close();
            conn.close();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }
 public int gettablerows(){
        int total=0;
        connect();
        try {
            
               
                SQLStatement = "SELECT COUNT(idorder) from orderr";

    
           
            rset = stmt.executeQuery(SQLStatement);
            while (rset.next()) {
                total = rset.getInt(1); // store result to total
            }
            rset.close();
            disconnect();
        } catch(Exception e){
            System.out.println(e.getMessage());
            JOptionPane.showMessageDialog(null,e.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
        return total;
    }

    public void erase(){
        for (int i=0; i<gettablerows(); i++){ // row count
            otable.setValueAt("", i, 0);
            otable.setValueAt("", i, 1);
            otable.setValueAt("", i, 2);
            otable.setValueAt("", i, 3);
            otable.setValueAt("", i, 4);
            otable.setValueAt("", i, 5);
            otable.setValueAt("", i, 6);
            otable.setValueAt("", i, 7);
            otable.setValueAt("", i, 8);
            otable.setValueAt("", i, 9);
            otable.setValueAt("", i, 10);
            otable.setValueAt("", i, 11);
            otable.setValueAt("", i, 12);
            otable.setValueAt("", i, 13);         
        }
    }
    public void searchbycname(){
        erase();
        connect();
        int i=0;
        
        try{
           SQLStatement = "SELECT idorder, o_dateposted, c_name, p_name, p_packing, s_quedan, o_qtysold, o_qtyreturned, o_datedelivered, o_drdate, o_drnum, o_wsnum, o_wrnum, o_notes "
            + "FROM product, stock, customer, order_stock, orderr "
            + "WHERE product.idproduct = stock.s_idproduct AND "
            + "orderr.o_idcustomer = customer.idcustomer AND "
            + "orderr.o_order_stock_id = idorder_stock AND "
            + "stock.idstock = order_stock.os_idstock AND "
            + "orderr.idorder = order_stock.os_idorder AND "
            + "customer.c_name LIKE '%"+ input +"%'";
            rset = stmt.executeQuery(SQLStatement);
            while (rset.next()) {
                otable.setValueAt(rset.getString("idorder"), i, 0);
                otable.setValueAt(rset.getString("o_dateposted"), i , 1);
                otable.setValueAt(rset.getString("c_name"), i, 2);
                otable.setValueAt(rset.getString("p_name"), i, 3);
                otable.setValueAt(rset.getString("p_packing"), i , 4);
                otable.setValueAt(rset.getString("s_quedan"), i, 5);
                otable.setValueAt(rset.getString("o_qtysold"), i , 6);
                otable.setValueAt(rset.getString("o_qtyreturned"), i, 7);
                otable.setValueAt(rset.getString("o_datedelivered"), i , 8);
                otable.setValueAt(rset.getString("o_drdate"), i, 9);
                otable.setValueAt(rset.getString("o_drnum"), i , 10);
                otable.setValueAt(rset.getString("o_wsnum"), i, 11);
                otable.setValueAt(rset.getString("o_wrnum"), i , 12);
                otable.setValueAt(rset.getString("o_notes"), i , 13);
                i++;
            }
            rset.close();
            disconnect();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }
    public void searchbypname(){
        erase();
        connect();
        int i=0;
        
        try{
           SQLStatement ="SELECT idorder, o_dateposted, c_name, p_name, p_packing, s_quedan, o_qtysold, o_qtyreturned, o_datedelivered, o_drdate, o_drnum, o_wsnum, o_wrnum, o_notes "
            + "FROM product, stock, customer, order_stock, orderr "
            + "WHERE product.idproduct = stock.s_idproduct AND "
            + "orderr.o_idcustomer = customer.idcustomer AND "
            + "orderr.o_order_stock_id = idorder_stock AND "
            + "stock.idstock = order_stock.os_idstock AND "
            + "orderr.idorder = order_stock.os_idorder AND "
            + "product.p_name LIKE '%"+ input +"%'";
            rset = stmt.executeQuery(SQLStatement);
            while (rset.next()) {
                otable.setValueAt(rset.getString("idorder"), i, 0);
                otable.setValueAt(rset.getString("o_dateposted"), i , 1);
                otable.setValueAt(rset.getString("c_name"), i, 2);
                otable.setValueAt(rset.getString("p_name"), i, 3);
                otable.setValueAt(rset.getString("p_packing"), i , 4);
                otable.setValueAt(rset.getString("s_quedan"), i, 5);
                otable.setValueAt(rset.getString("o_qtysold"), i , 6);
                otable.setValueAt(rset.getString("o_qtyreturned"), i, 7);
                otable.setValueAt(rset.getString("o_datedelivered"), i , 8);
                otable.setValueAt(rset.getString("o_drdate"), i, 9);
                otable.setValueAt(rset.getString("o_drnum"), i , 10);
                otable.setValueAt(rset.getString("o_wsnum"), i, 11);
                otable.setValueAt(rset.getString("o_wrnum"), i , 12);
                otable.setValueAt(rset.getString("o_notes"), i , 13);
                i++;
            }
                rset.close();
                 disconnect();
        
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }
    public void searchbydateposted(){
        erase();
        connect();
        int i=0;

        if (input.length()!=8)
            input = date.convertDate(input);
        
        try{
           SQLStatement ="SELECT idorder, o_dateposted, c_name, p_name, p_packing, s_quedan, o_qtysold, o_qtyreturned, o_datedelivered, o_drdate, o_drnum, o_wsnum, o_wrnum, o_notes "
            + "FROM product, stock, customer, order_stock, orderr "
            + "WHERE product.idproduct = stock.s_idproduct AND "
            + "orderr.o_idcustomer = customer.idcustomer AND "
            + "orderr.o_order_stock_id = idorder_stock AND "
            + "stock.idstock = order_stock.os_idstock AND "
            + "orderr.idorder = order_stock.os_idorder AND "
            + "o_dateposted = '" +input+ "'" ;
            rset = stmt.executeQuery(SQLStatement);
             while (rset.next()) {
                otable.setValueAt(rset.getString("idorder"), i, 0);
                otable.setValueAt(rset.getString("o_dateposted"), i , 1);
                otable.setValueAt(rset.getString("c_name"), i, 2);
                otable.setValueAt(rset.getString("p_name"), i, 3);
                otable.setValueAt(rset.getString("p_packing"), i , 4);
                otable.setValueAt(rset.getString("s_quedan"), i, 5);
                otable.setValueAt(rset.getString("o_qtysold"), i , 6);
                otable.setValueAt(rset.getString("o_qtyreturned"), i, 7);
                otable.setValueAt(rset.getString("o_datedelivered"), i , 8);
                otable.setValueAt(rset.getString("o_drdate"), i, 9);
                otable.setValueAt(rset.getString("o_drnum"), i , 10);
                otable.setValueAt(rset.getString("o_wsnum"), i, 11);
                otable.setValueAt(rset.getString("o_wrnum"), i , 12);
                otable.setValueAt(rset.getString("o_notes"), i , 13);
                i++;
            }
                rset.close();
                 disconnect();
        
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }
}
